<?php
/*
*Return user counties comma separated as string 
*/
function getUserCountiesList($user){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$countiesArray = array();
	$stringCountiesList = mysqli_query($dbc,
						"SELECT `judete` FROM `utilizatori` where `mail`='$user'");
	if ($stringCountiesList &&$stringCountiesList->num_rows != 0){
		$stringCountiesListRetrieved = mysqli_fetch_assoc($stringCountiesList);
		$countiesList = $stringCountiesListRetrieved["judete"];
		//$countiesArray = explode(",", $countiesList);
	}				
	return $countiesList;
}
/*
* Return list of manufacturers from $countyList and with state_anc = respins_anc
* countyList - string containing county nr comma separated
*/
function getAncRejectedM($countyList){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$m =  mysqli_query($dbc,
						"SELECT  `nume`, `CUI_CF`,`comentarii`  FROM `furnizor`
						 where `cod_judet` in ($countyList) AND `state_anc`='respins_anc' ORDER BY `date_modified` DESC") ;				
	return $m;
}

/*
* Return list of programs from $countyList and with state_anc = respins_anc
* countyList - string containing county nr comma separated
*/
function getAncRejectedP($cuiList){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$p =  mysqli_query($dbc,
		"SELECT `nume_program`,`CUI_CF`,`comentarii` FROM furnizori_programe WHERE `CUI_CF` IN ($cuiList) AND `state_anc`='respins_anc' ORDER BY `date_modified` DESC");				
	return $p;
}

/*
*  Return the region of the given county
*/
function newDBC(){
	$dbc = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
	if (!mysqli_set_charset($dbc, "utf8"))
	{
		printf("Error loading character set utf8: %s\n", mysqli_error($dbc));
	}
	return $dbc;
}

function getRegion($county){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$countyIdQuery = mysqli_query($dbc,"SELECT id from `account_county` where name = '$county';");
	$countyId = -1;
	
	if ($countyIdQuery && $countyIdQuery->num_rows != 0){
		$countyRetrieved = mysqli_fetch_assoc($countyIdQuery);
		$countyId =  $countyRetrieved["id"];
	}
	
	$region = "" ;	
	if ($countyId != -1){
		$regionQuery = mysqli_query($dbc,"SELECT `region` from  `account_city` where `county_id` = '$countyId';");
		if ($regionQuery && $regionQuery->num_rows != 0){
			$entryRetrieved = mysqli_fetch_assoc($regionQuery);
			$region = $entryRetrieved["region"];
		}
	} 
	return $region;
	
}

/*
* Returns the provired(manufacturer) with CUI_CF=$var_cui_furnizor
*/
function getFurnizor($var_cui_furnizor){
	
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$furnizor = mysqli_query($dbc,
						"SELECT * FROM `furnizor` WHERE `CUI_CF`='$var_cui_furnizor'");
	return $furnizor;
}
/*
* Checks that a user email exist in Utilizatori table
* Returns 1 is user email exists, 0 if it doesn't
*/
function userEmailExists($userEmail){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$emailQuery = mysqli_query($dbc,
						"SELECT * FROM `utilizatori` WHERE `mail`='$userEmail'");
	if ($emailQuery && $emailQuery->num_rows != 0)	
		return 1;
	return 0;
}

/*
* Returns the counties available 
*/
function getCounties() {
	
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$countiesQuery = mysqli_query($dbc,
						"SELECT * FROM `account_county`");
	$counties = array();
	if ($countiesQuery && $countiesQuery->num_rows != 0){
		while ($county = mysqli_fetch_assoc($countiesQuery))
		 array_push($counties,$county["name"] );
	}
	return $counties;
}

/*
* Returns all localities available for a given county 
*/
function getLocalities($county) {
	
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$localitiesQuery = mysqli_query($dbc,
						"SELECT a.name FROM `account_city` a, `account_county` b 
where b.name = '$county' &&  a.county_id = b.id;");
	$localities = array();
	if ($localitiesQuery && $localitiesQuery->num_rows != 0){
		while ($locality = mysqli_fetch_assoc($localitiesQuery))
		 array_push($localities,$locality["name"] );
	}
	return $localities;
}
/*
* Returns an id for a given county or -1 of the county cannont be found
*/
function getCountyId($county){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$countyIdQuery = mysqli_query($dbc,"SELECT id from `account_county` where name = '$county';");
	
	if ($countyIdQuery && $countyIdQuery->num_rows != 0){
		$countyRetrieved = mysqli_fetch_assoc($countyIdQuery);
		return $countyRetrieved["id"];
	}
	else 
		return -1;
}

/*
* Return all counties with associated ids
*/

function getAllCountiesAndIds($dbc){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$countiesAndIdsQuery = mysqli_query($dbc, "SELECT id, name from `account_county`;");
	return $countiesAndIdsQuery;
}
/*
   Returns true if $judet is present in User's judete list.
*/
function isUserEmailJudet($userEmail, $judet){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$emailQuery = mysqli_query($dbc,
						"SELECT judete FROM `utilizatori` WHERE `mail`='$userEmail'");
	//if ($emailQuery && $emailQuery->num_rows != 0)	
	//	return 1;
	$countiesAndIdsQuery1 = getAllCountiesAndIds($dbc);
	while($countyRetrv1 = mysqli_fetch_assoc($countiesAndIdsQuery1)){
			$localCounties1[$countyRetrv1["id"]] = $countyRetrv1["name"];
	}
	$emailresult =mysqli_fetch_assoc($emailQuery);
	$countiesIds1 = explode(",",$emailresult['judete']);
	
	if (isset($countiesIds1)  && !empty($countiesIds1) ){
		foreach($countiesIds1 as $countyId1)
			if ($countyId1 != "" && $countyId1 != " ")
				if (!strcmp($localCounties1[$countyId1] , $judet)) //we found the judet in the user's list
					return 1; //display it
	}
	return 0;
}
/*
*   Return judet name of furnizor based on id_furnizor_program
*/
function getProgramJudet( $cui_cf ){
    require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$query = mysqli_query($dbc,
						"SELECT judet FROM `furnizor` WHERE `CUI_CF`='$cui_cf'");
    $result = mysqli_fetch_assoc($query);
	return $result['judet'];
}
/*
	Return CUI_CF from id_program_furnizor
*/
function getCUICF ( $id_program_furnizor )
{
    require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
	$query = mysqli_query($dbc,
						"SELECT CUI_CF FROM `furnizori_programe` WHERE `id_furnizori_programe`='$id_program_furnizor'");
	$result = mysqli_fetch_assoc($query);
	return $result['CUI_CF'];
}
/* 
* Return judet name of session 
*/
function getSessionJudet( $id_program_furnizor ){
    require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	$dbc = newDBC();
    $cui_cf = getCUICF($id_program_furnizor);
	$query = mysqli_query($dbc,
						"SELECT judet FROM `furnizor` WHERE `CUI_CF`='$cui_cf'");
    $result = mysqli_fetch_assoc($query);
	return $result['judet'];
}	
/*
*  Return a json object with the format-  {cornc:[{denumire:numeProgram , cod:12}, {denumire:numeProgram2, cod: cod2}]}
*/

function getAllCorNc(){
	require_once($_SERVER['DOCUMENT_ROOT'].'/mysql_connect.php');
	
	$dbc = newDBC();
	$corNcQuery = mysqli_query($dbc,"SELECT * FROM `cor` UNION SELECT * FROM `nc`;");
	$corNcAllElements = array();
	if ($corNcQuery && $corNcQuery->num_rows != 0){
		while ($corNcEntry = mysqli_fetch_assoc($corNcQuery)){
			
				$corNcElement =  array();
				$corNcElement["denumire"] = $corNcEntry["denumire"];
				$corNcElement["cod"] = $corNcEntry["cod"];
				
				 array_push($corNcAllElements ,$corNcElement);
		
		}
	}
	$corNcJson = array();
	$corNcJson["corNc"] = $corNcAllElements ;
	
	return $corNcJson;
	
}


if(isset($_POST['CUI_CF']) && isset($_POST['checkCUI'])  )
 	{
	  $var_cui_furnizor = mysql_real_escape_string($_POST['CUI_CF']);
	  $ret = "0";
	  
      $furnizor = getFurnizor($var_cui_furnizor);
	  if ( $furnizor && $furnizor->num_rows != 0 )
		$ret = "1"; 
     
      echo $ret;
	}
	 
	 
if(isset($_POST['counties']))
	{
      $counties = getCounties();
	  $comma_counties = implode(",", $counties);
      echo $comma_counties ;
	}

if(isset($_POST['getLocalities']))
	{
      $localities = getLocalities($_POST['county']);
	  $comma_localities = implode(",", $localities);
      echo $comma_localities ;
	}
	
if(isset($_POST['getCountyId']))
	{
       echo getCountyId($_POST['county']);
	}
	
if (isset($_POST['userEmailExists'])){
	echo userEmailExists($_POST['userEmailExists']);
}

if (isset($_GET['getCorNc'])){
	header("Content-Type: application/json", true);
	echo json_encode(getAllCorNc()); 
	exit;
	
}
?>